package test.aat.dxfy.demo01;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import com.aat.dxfy.utils.Atools;
import com.aat.dxfy.utils.POIUtil;

public class ReadExcel {
	public static void main(String[] args) {
		try {
			List<Map<Integer, String>> lists = POIUtil
					.readExcelContent("E:\\1.xls");
			for (Map<Integer, String> map : lists) {
				//
				String onlineTime = "";
				String offLineTime = "";
				Long shengYuTime = 0l;
				try {
					String s = map.get(8);
					String s1 = "2015/07/10";
					onlineTime = s.substring(0, s1.length()).replaceAll("/",
							"-");
					offLineTime = s.substring(s1.length() + 1, s.length())
							.replaceAll("/", "-");
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					Date d1 = sdf.parse(offLineTime);
					shengYuTime = d1.getTime();
				} catch (Exception e) {

				}

				//
				Integer kickMoney = 0;
				Integer yiChouMoney = 0;
				Integer shenYuMoney = 0;
				Integer process = 0;
				try {
					String s = map.get(5).replaceAll("￥", "");
					kickMoney = Integer.parseInt(s);
					String s1 = map.get(6).replaceAll("￥", "");
					yiChouMoney = Integer.parseInt(s1);
					shenYuMoney = kickMoney - yiChouMoney;
					process = yiChouMoney * 100 / kickMoney;

				} catch (Exception e) {

				}

				//
				Integer zanCount = 0;
				try {
					String s = map.get(10);
					zanCount = Integer.parseInt(s);

				} catch (Exception e) {

				}
				//

				String kickImgs = "";
				String kickTitle = map.get(2);

				Integer readCount = 0;
				Integer minMoney = 0;
				Integer maxPerson = 0;
				Integer remainPerson = 0;
				String kickSimple = "";
				String peiSong = "";
				String huiBaoTime = "";

				String remark = map.get(4);
				String kickUrl = map.get(3);
				String kickMan = "";
				String otherInfo = "";
				String flag = "";

				String platformUrl = map.get(1);
				String kickStartMan = map.get(7);

				String kickStartManSimple = map.get(11);
				String kickContext = "";
				try{
					String[] ss=map.get(12).split(" ");
					for (String s : ss) {
						String s1 = "<img src=\""+s+"\" />";
						kickContext+=s1;
					}
				}catch (Exception e) {
					
				}

				String addSql = "INSERT INTO [weberongjin].[dbo].[web_kick] (";
				addSql += "[id],[kickType1],[kickType2],[platformName],[onlineTime],";
				addSql += "[zanCount],[kickImgs],[kickTitle],[kickMoney],[yiChouMoney],";
				addSql += "[shenYuMoney],[process],[shengYuTime],[readCount],[minMoney],";
				addSql += "[maxPerson],[remainPerson],[kickSimple],[peiSong],[huiBaoTime],";
				addSql += "[remark],[kickUrl],[kickMan],[otherInfo],[flag],";
				addSql += "[platformUrl],[kickStartMan],[offLineTime],[kickStartManSimple],[kickContext],";
				addSql += "[isJoin]) VALUES (";
				addSql += "'" + Atools.getOneKeyS() + "','7','1','京东众筹','"
						+ onlineTime + "',";
				addSql += "" + zanCount + ",'" + kickImgs + "','" + kickTitle
						+ "'," + kickMoney + "," + yiChouMoney + ",";
				addSql += "" + shenYuMoney + "," + process + "," + shengYuTime
						+ "," + readCount + "," + minMoney + ",";
				addSql += "" + maxPerson + "," + remainPerson + ",'"
						+ kickSimple + "','" + peiSong + "','" + huiBaoTime
						+ "',";
				addSql += "'" + remark + "','" + kickUrl + "','" + kickMan
						+ "','" + otherInfo + "','" + flag + "',";
				addSql += "'" + platformUrl + "','" + kickStartMan + "','"
						+ offLineTime + "','" + kickStartManSimple + "','"
						+ kickContext + "',";
				addSql += "'0');";
				System.out.println(addSql);
				//
			}
		} catch (Exception e) {
			e.printStackTrace();
			//delete  FROM [weberongjin].[dbo].[web_kick] where kickTitle in ('支教偏远山区 为爱众筹','为爱夜行，关爱留守儿童','为了留守儿童乡村教师要培训','中国印第安人：基诺采遗之旅','一份来自青春的请柬','伸出援手，圆乡村孩子建桥梦','改善中国式蜜蜂生存环境','为单亲脑瘫儿捐赠医疗费','圆山里小艺术家的北京展演梦','和谢霆锋一起做公益','孤儿健康成长，需要你我守护','为藏区孩子筹个图书角','支教偏远山区 为爱众筹');
		}
	}

	// 增加众筹项目
	public static void addKick() {
		String onlineTime = "2015-07-10";
		Integer zanCount = 133;
		String kickImgs = "";
		String kickTitle = "";
		Integer kickMoney = 0;
		Integer yiChouMoney = 0;
		Integer shenYuMoney = 0;
		Integer process = 0;
		Long shengYuTime = 0L;
		Integer readCount = 0;
		Integer minMoney = 0;
		Integer maxPerson = 0;
		Integer remainPerson = 0;
		String kickSimple = "";
		String peiSong = "";
		String huiBaoTime = "";

		String remark = "";
		String kickUrl = "";
		String kickMan = "";
		String otherInfo = "";
		String flag = "";

		String platformUrl = "";
		String kickStartMan = "";
		String offLineTime = "";
		String kickStartManSimple = "";
		String kickContext = "";

		String addSql = "INSERT INTO [weberongjin].[dbo].[web_kick] (";
		addSql += "[id],[kickType1],[kickType2],[platformName],[onlineTime],";
		addSql += "[zanCount],[kickImgs],[kickTitle],[kickMoney],[yiChouMoney],";
		addSql += "[shenYuMoney],[process],[shengYuTime],[readCount],[minMoney],";
		addSql += "[maxPerson],[remainPerson],[kickSimple],[peiSong],[huiBaoTime],";
		addSql += "[remark],[kickUrl],[kickMan],[otherInfo],[flag],";
		addSql += "[platformUrl],[kickStartMan],[offLineTime],[kickStartManSimple],[kickContext],";
		addSql += "[isJoin]) VALUES (";
		addSql += "'" + Atools.getOneKeyS() + "','7','1','京东众筹','" + onlineTime
				+ "',";
		addSql += "" + zanCount + ",'" + kickImgs + "','" + kickTitle + "',"
				+ kickMoney + "," + yiChouMoney + ",";
		addSql += "" + shenYuMoney + "," + process + "," + shengYuTime + ","
				+ readCount + "," + minMoney + ",";
		addSql += "" + maxPerson + "," + remainPerson + ",'" + kickSimple
				+ "','" + peiSong + "','" + huiBaoTime + "',";
		addSql += "'" + remark + "','" + kickUrl + "','" + kickMan + "','"
				+ otherInfo + "','" + flag + "',";
		addSql += "'" + platformUrl + "','" + kickStartMan + "','"
				+ offLineTime + "','" + kickStartManSimple + "','"
				+ kickContext + "',";
		addSql += "'0';";
		System.out.println(addSql);
	}

	// 增加众筹支持

	// 连接数据库
	private static Connection getSqlServerCon() throws Exception {
		String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		String url = "jdbc:sqlserver://211.103.170.170:1433; DatabaseName=weberongjin";
		String user = "wx";
		String passwd = "wx123.";
		Class.forName(driver);
		return DriverManager.getConnection(url, user, passwd);
	}

	// 查询数据库
	public static void queryZC() {
		try {
			Connection c = getSqlServerCon();

			// 查询众筹
			String sqlFindZC = "select * from web_kick";
			PreparedStatement ps = c.prepareStatement(sqlFindZC);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getString(1));
			}

			rs.close();
			c.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 读取excel
	public static void readExcel() {
		try {
			List<Map<Integer, String>> lists = POIUtil
					.readExcelContent("E:\\1.xls");
			for (Map<Integer, String> map : lists) {
				Set<Entry<Integer, String>> entry = map.entrySet();
				for (Entry<Integer, String> e : entry) {
					System.out.println(e.getKey() + ":" + e.getValue());
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

}
